/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao.impl;

import dao.ContendorDao;
import entity.Contendor;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

/**
 *
 * @author USER
 */
public class ContendorDaoImpl implements ContendorDao {

    private DriverManagerDataSource dataSource;

    public void setDataSource(DriverManagerDataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public Integer insertar(Contendor Contendor) {
        Integer result = null;
        Double maxcod = 0.0;
        String conteo = "select max(Codcon) from mkt_proyecto_Contendor";
        String sql = "";
        Connection cn = null;
        String fechaReg = Contendor.getFecreg().substring(8, 10) + "/" + Contendor.getFecreg().substring(3, 5) + "/" + Contendor.getFecreg().substring(0, 2);

        try {
            cn = dataSource.getConnection();
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(conteo);
            while (rs.next()) {
                maxcod = rs.getDouble(1);
            }
            maxcod = maxcod + 1;
            String maxnumber = maxcod.toString();
            sql = "insert into mkt_proyecto_Contendor(codcon,nomcon,tipcon,imacon,texcon,canele,usureg,fecreg) values ('" + maxcod + "','" + Contendor.getNomcon() + "','" + Contendor.getTipcon() + "','" + Contendor.getImacon() + "','" + Contendor.getTexcon() + "'," + Contendor.getCanele() + ",'" + "Test1" + "','" + fechaReg + "')";
            int ctos = st.executeUpdate(sql);
            if (ctos == 0) {
                result = 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    @Override
    public void actualizar(Contendor Contendor) {
        String result = null;
        String sql = "update mkt_proyecto_Contendor set nomcon='" + Contendor.getNomcon() + "', tipcon='" + Contendor.getTipcon() + "',imacon='" + Contendor.getImacon() + "',texcon='" + Contendor.getTexcon() + "',canele=" + Contendor.getCanele() + " where codcon=" + Contendor.getCodcon();
        Connection cn = null;

        try {
            cn = dataSource.getConnection();
            Statement st = cn.createStatement();
            int ctos = st.executeUpdate(sql);
            if (ctos == 0) {
                result = "0 filas afectadas";
            }
        } catch (SQLException e) {
            result = e.getMessage();
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
                result = e.getMessage();
            }
        }
        System.out.println(result);
    }

    @Override
    public void eliminar(Double id) {
        String result = null;
        String sql = "delete from mkt_proyecto_Contendor where codcon=" + id;
        Connection cn = null;

        try {
            cn = dataSource.getConnection();
            Statement st = cn.createStatement();
            int ctos = st.executeUpdate(sql);
            if (ctos == 0) {
                result = "0 filas afectadas";
            }
        } catch (SQLException e) {
            result = e.getMessage();
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
                result = e.getMessage();
            }
        }
        System.out.println(result);
    }

    @Override
    public List<Contendor> buscarTodos() {
        List<Contendor> list = null;
        String sql = "select codcon,nomcon,tipcon,imacon,texcon,canele,usureg,fecreg from mkt_proyecto_Contendor";
        Connection cn = null;

        try {
            cn = dataSource.getConnection();
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            list = new ArrayList<Contendor>();
            while (rs.next()) {
                Contendor Contendor = new Contendor();
                Contendor.setCodcon(rs.getDouble(1));
                Contendor.setNomcon(rs.getString(2));
                Contendor.setTipcon(rs.getString(3));
                Contendor.setImacon(rs.getString(4));
                Contendor.setTexcon(rs.getString(5));
                Contendor.setCanele(rs.getDouble(6));
                Contendor.setUsureg(rs.getString(7));
                Contendor.setFecreg(rs.getString(8));
                list.add(Contendor);
            }
        } catch (SQLException e) {
            list = null;
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }
        return list;
    }

    @Override
    public List<Contendor> buscar(Contendor Contendor) {
        List<Contendor> list = null;
        String sql = "select codcon,nomcon,tipcon,imacon,texcon,canele,usureg,fecreg from mkt_proyecto_Contendor where Nomcon like '%" + Contendor.getNomcon() + "%'";
        Connection cn = null;
        try {
            cn = dataSource.getConnection();
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            list = new ArrayList<Contendor>();
            while (rs.next()) {
                Contendor Contendor1 = new Contendor();
                Contendor1.setCodcon(rs.getDouble(1));
                Contendor1.setNomcon(rs.getString(2));
                Contendor1.setTipcon(rs.getString(3));
                Contendor1.setImacon(rs.getString(4));
                Contendor1.setTexcon(rs.getString(5));
                Contendor1.setCanele(rs.getDouble(6));
                Contendor1.setUsureg(rs.getString(7));
                Contendor1.setFecreg(rs.getString(8));
                list.add(Contendor1);
            }
        } catch (SQLException e) {
            list = null;
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }
        return list;
    }

    @Override
    public Contendor buscar(Double id) {
        Contendor Contendor = null;
        String sql = "select codcon,nomcon,tipcon,imacon,texcon,canele,usureg,fecreg from mkt_proyecto_Contendor where codcon =" + id;
        Connection cn = null;


        try {
            cn = dataSource.getConnection();
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            if (rs.next()) {
                Contendor = new Contendor();
                Contendor.setCodcon(rs.getDouble(1));
                Contendor.setNomcon(rs.getString(2));
                Contendor.setTipcon(rs.getString(3));
                Contendor.setImacon(rs.getString(4));
                Contendor.setTexcon(rs.getString(5));
                Contendor.setCanele(rs.getDouble(6));
                Contendor.setUsureg(rs.getString(7));
                Contendor.setFecreg(rs.getString(8));
            }
        } catch (SQLException e) {
            Contendor = null;
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }
        return Contendor;
    }

    @Override
    public List<Contendor> buscarPorNombre(String nombre) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
    
    @Override
    public List<Contendor> buscarNivel1(Double id) {
        List<Contendor> list = null;
        String sql = "select d.codcon,c.nomcon,c.tipcon,c.imacon,c.texcon,c.canele,c.usureg,c.fecreg,d.codtor from mkt_proyecto_Contendor c,mkt_proyecto_detalle d where c.codcon=d.codcon and d.ganador<7 and d.codtor=' " + id + "' order by d.pareja desc";
        Connection cn = null;

        try {
            cn = dataSource.getConnection();
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            list = new ArrayList<Contendor>();
            while (rs.next()) {
                Contendor Contendor = new Contendor();
                Contendor.setCodcon(rs.getDouble(1));
                Contendor.setNomcon(rs.getString(2));
                Contendor.setTipcon(rs.getString(3));
                Contendor.setImacon(rs.getString(4));
                Contendor.setTexcon(rs.getString(5));
                Contendor.setCanele(rs.getDouble(6));
                Contendor.setUsureg(rs.getString(7));
                Contendor.setFecreg(rs.getString(8));
                Contendor.setCodtor(rs.getDouble(9));
                list.add(Contendor);
            }
        } catch (SQLException e) {
            list = null;
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }
        return list;
    }
    
    @Override
    public List<Contendor> buscarNivel2(Double id) {
        List<Contendor> list = null;
        String sql = "select d.codcon,c.nomcon,c.tipcon,c.imacon,c.texcon,c.canele,c.usureg,c.fecreg,d.codtor from mkt_proyecto_Contendor c,mkt_proyecto_detalle d where c.codcon=d.codcon and d.ganador>0 and d.ganador<7 and d.codtor=' " + id + "' order by d.pareja desc";
        Connection cn = null;

        try {
            cn = dataSource.getConnection();
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            list = new ArrayList<Contendor>();
            while (rs.next()) {
                Contendor Contendor = new Contendor();
                Contendor.setCodcon(rs.getDouble(1));
                Contendor.setNomcon(rs.getString(2));
                Contendor.setTipcon(rs.getString(3));
                Contendor.setImacon(rs.getString(4));
                Contendor.setTexcon(rs.getString(5));
                Contendor.setCanele(rs.getDouble(6));
                Contendor.setUsureg(rs.getString(7));
                Contendor.setFecreg(rs.getString(8));
                Contendor.setCodtor(rs.getDouble(9));
                list.add(Contendor);
            }
        } catch (SQLException e) {
            list = null;
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }
        return list;
    }
    
    @Override
    public List<Contendor> buscarNivel3(Double id) {
        List<Contendor> list = null;
        String sql = "select d.codcon,c.nomcon,c.tipcon,c.imacon,c.texcon,c.canele,c.usureg,c.fecreg,d.codtor from mkt_proyecto_Contendor c,mkt_proyecto_detalle d where c.codcon=d.codcon and d.ganador>1 and d.ganador<7 and d.codtor=' " + id + "' order by d.pareja desc";
        Connection cn = null;

        try {
            cn = dataSource.getConnection();
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            list = new ArrayList<Contendor>();
            while (rs.next()) {
                Contendor Contendor = new Contendor();
                Contendor.setCodcon(rs.getDouble(1));
                Contendor.setNomcon(rs.getString(2));
                Contendor.setTipcon(rs.getString(3));
                Contendor.setImacon(rs.getString(4));
                Contendor.setTexcon(rs.getString(5));
                Contendor.setCanele(rs.getDouble(6));
                Contendor.setUsureg(rs.getString(7));
                Contendor.setFecreg(rs.getString(8));
                Contendor.setCodtor(rs.getDouble(9));
                list.add(Contendor);
            }
        } catch (SQLException e) {
            list = null;
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }
        return list;
    }
    
    @Override
    public List<Contendor> buscarNivel4(Double id) {
        List<Contendor> list = null;
        String sql = "select d.codcon,c.nomcon,c.tipcon,c.imacon,c.texcon,c.canele,c.usureg,c.fecreg,d.codtor from mkt_proyecto_Contendor c,mkt_proyecto_detalle d where c.codcon=d.codcon and d.ganador>2 and d.ganador<7 and d.codtor=' " + id + "' order by d.pareja desc";
        Connection cn = null;

        try {
            cn = dataSource.getConnection();
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            list = new ArrayList<Contendor>();
            while (rs.next()) {
                Contendor Contendor = new Contendor();
                Contendor.setCodcon(rs.getDouble(1));
                Contendor.setNomcon(rs.getString(2));
                Contendor.setTipcon(rs.getString(3));
                Contendor.setImacon(rs.getString(4));
                Contendor.setTexcon(rs.getString(5));
                Contendor.setCanele(rs.getDouble(6));
                Contendor.setUsureg(rs.getString(7));
                Contendor.setFecreg(rs.getString(8));
                Contendor.setCodtor(rs.getDouble(9));
                list.add(Contendor);
            }
        } catch (SQLException e) {
            list = null;
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }
        return list;
    }
    
    @Override
    public List<Contendor> buscarNivel5(Double id) {
        List<Contendor> list = null;
        String sql = "select d.codcon,c.nomcon,c.tipcon,c.imacon,c.texcon,c.canele,c.usureg,c.fecreg,d.codtor from mkt_proyecto_Contendor c,mkt_proyecto_detalle d where c.codcon=d.codcon and d.ganador>3 and d.ganador<7 and d.codtor=' " + id + "' order by d.pareja desc";
        Connection cn = null;

        try {
            cn = dataSource.getConnection();
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            list = new ArrayList<Contendor>();
            while (rs.next()) {
                Contendor Contendor = new Contendor();
                Contendor.setCodcon(rs.getDouble(1));
                Contendor.setNomcon(rs.getString(2));
                Contendor.setTipcon(rs.getString(3));
                Contendor.setImacon(rs.getString(4));
                Contendor.setTexcon(rs.getString(5));
                Contendor.setCanele(rs.getDouble(6));
                Contendor.setUsureg(rs.getString(7));
                Contendor.setFecreg(rs.getString(8));
                Contendor.setCodtor(rs.getDouble(9));
                list.add(Contendor);
            }
        } catch (SQLException e) {
            list = null;
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }
        return list;
    }
    
    @Override
    public List<Contendor> buscarNivel6(Double id) {
        List<Contendor> list = null;
        String sql = "select d.codcon,c.nomcon,c.tipcon,c.imacon,c.texcon,c.canele,c.usureg,c.fecreg,d.codtor from mkt_proyecto_Contendor c,mkt_proyecto_detalle d where c.codcon=d.codcon and d.ganador>4 and d.ganador<7 and d.codtor=' " + id + "' order by d.pareja desc";
        Connection cn = null;

        try {
            cn = dataSource.getConnection();
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            list = new ArrayList<Contendor>();
            while (rs.next()) {
                Contendor Contendor = new Contendor();
                Contendor.setCodcon(rs.getDouble(1));
                Contendor.setNomcon(rs.getString(2));
                Contendor.setTipcon(rs.getString(3));
                Contendor.setImacon(rs.getString(4));
                Contendor.setTexcon(rs.getString(5));
                Contendor.setCanele(rs.getDouble(6));
                Contendor.setUsureg(rs.getString(7));
                Contendor.setFecreg(rs.getString(8));
                Contendor.setCodtor(rs.getDouble(9));
                list.add(Contendor);
            }
        } catch (SQLException e) {
            list = null;
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }
        return list;
    }
    
    @Override
    public List<Contendor> buscarNivel7(Double id) {
        List<Contendor> list = null;
        String sql = "select d.codcon,c.nomcon,c.tipcon,c.imacon,c.texcon,c.canele,c.usureg,c.fecreg,d.codtor from mkt_proyecto_Contendor c,mkt_proyecto_detalle d where c.codcon=d.codcon and d.ganador>5 and d.ganador<7 and d.codtor=' " + id + "' order by d.pareja desc";
        Connection cn = null;

        try {
            cn = dataSource.getConnection();
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            list = new ArrayList<Contendor>();
            while (rs.next()) {
                Contendor Contendor = new Contendor();
                Contendor.setCodcon(rs.getDouble(1));
                Contendor.setNomcon(rs.getString(2));
                Contendor.setTipcon(rs.getString(3));
                Contendor.setImacon(rs.getString(4));
                Contendor.setTexcon(rs.getString(5));
                Contendor.setCanele(rs.getDouble(6));
                Contendor.setUsureg(rs.getString(7));
                Contendor.setFecreg(rs.getString(8));
                Contendor.setCodtor(rs.getDouble(9));
                list.add(Contendor);
            }
        } catch (SQLException e) {
            list = null;
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }
        return list;
    }
    
    @Override
    public void pasarRonda(Double idtor, Double idcon, Integer codGanador) {
        String result = null;
        String sql = "update mkt_proyecto_detalle set ganador='" + codGanador + "' where codcon=" + idcon + " and codtor=" + idtor;
        Connection cn = null;

        try {
            cn = dataSource.getConnection();
            Statement st = cn.createStatement();
            int ctos = st.executeUpdate(sql);
            if (ctos == 0) {
                result = "0 filas afectadas";
            }
        } catch (SQLException e) {
            result = e.getMessage();
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
                result = e.getMessage();
            }
        }
        System.out.println(result);
    }
    
    
}
